Національний технічний університет України
«Київський політехнічний інститут імені Ігоря Сікорського»
Теплоенергетичний факультет
Кафедра автоматизації проектування енергетичних процесів і систем
ЛАБОРАТОРНА РОБОТА №5
з дисципліни «Системи баз даних»
Варіант № 9
Тема: Використання виразу CASE у вибірках даних. Оператор UNION.
Мета: Навчитися застосовувати умовний оператор в команді SELECT
Завдання:
написати пошуковий запит SELECT з використанням двох типів виразу CASE(простий вираз / логічний вираз);
написати запит з використанням інструкції IIF / IF (MS SQL / MySQL);
написати запит SELECT з використанням CASE або IIF в різних розділах
пошукового запиту (SELECT, IN, WHERE, ORDER BY, HAVING)
навести приклади використання CASE або IIF в інших запитах (UPDATE, DELETE)
написати запит з використанням функції CHOOSE / COALESCE (MS SQL /MySQL);
Написати запит з використанням оператора UNION. Пояснити відмінність UNION від JOIN.
Скрипт:
#Створення бази даних
CREATE DATABASE IF NOT EXISTS lab;
USE lab;
#Створення таблиці відділів
create table IF NOT EXISTS department(
ID_department INT auto_increment primary key,
Department varchar(30) not null unique
) engine = INNODB;
#Створення таблиці працівників
CREATE TABLE IF NOT EXISTS person (
ID_person INT AUTO_INCREMENT PRIMARY KEY,
Connector_ID_department INT,
FOREIGN KEY (Connector_ID_department)
REFERENCES department(ID_department),
Table_number INT NOT NULL UNIQUE,
FIO_person VARCHAR(50) NOT NULL,
Salary INT CHECK (salary > 0),
Gained BOOL # Отримав або ні
) ENGINE=INNODB;
create table IF NOT EXISTS money(
ID_money INT auto_increment primary key,
Connector_ID_person INT,
FOREIGN KEY(Connector_ID_person)
REFERENCES person(ID_person),
Code_money INT unique not null,
Amount_money Int not null,
Type_money varchar(50)
) engine = INNODB;
#Заповнення таблиці відділів
INSERT INTO department (Department)
VALUES
('Marketing department'),
('Sales department'),
('Production department');
#Заповнення таблиці працівників
INSERT INTO person (Connector_ID_department, Table_number, FIO_person, Salary, Gained)
VALUES
(1, 0001, 'Medvedev I.O.', 1900, true),
(2, 0002, 'Ivanov V.A.', 2100, true),
(3, 0003, 'Smirnov K.L.', 1800, true),
(2, 0004, 'Petrov G.G.', 1850, false),
(2, 0005, 'Kykyryzov K.I.', 1950, true),
(3, 0006, 'Smirnov K.L.', 2050, false);
#Заповнення таблиці надходжень
INSERT INTO money (Connector_ID_person, Code_money, Amount_money, Type_money)
VALUES
(1, 001, 1900, 'Earnings'),
(2, 002, 200, 'Award'),
(3, 101, 100, 'Penalty for inappropriate behavior'),
(4, 102, 50, 'Late penalty');
# Завдання - 1
SELECT * ,
CASE Connector_ID_department
WHEN 1 THEN "Marketing department"
WHEN 2 THEN "Sales department"
WHEN 3 THEN "Production department"
END AS Department_name
FROM person;
SELECT * ,
CASE
WHEN Salary <1900 THEN "Novice worker"
WHEN Salary>= 1900 OR Salary <2500 THEN "Avarage worker"
ELSE "Good worker"
END AS WorkerRank
FROM person;
# Завдання - 2
SELECT * ,
IF( Connector_ID_department = 3, "1-st floor","2-nd floor") AS OfficeFloor
FROM person;
# Завдання - 3
SELECT Connector_ID_department, count(Connector_ID_department),
IF( Connector_ID_department = 3, "1-st floor","2-nd floor") AS OfficeFloor
FROM person
GROUP BY Connector_ID_department HAVING count(Connector_ID_department)
ORDER BY Connector_ID_department;
# Завдання - 4
DELETE FROM person
WHERE 1 = CASE
WHEN Gained = 1 AND Salary >=1900 THEN 1
ELSE 0
END;
UPDATE person
SET Gained = 1
WHERE 1 = CASE
WHEN Gained = 0 AND Salary >=1900 THEN 1
ELSE 0
END;
# Завдання - 5
#Додамо для демонстрації
INSERT INTO person (Connector_ID_department, Table_number, FIO_person, Salary, Gained)
VALUES(1, 0007, 'Minkov K.K.', NULL , true);
SELECT*,
COALESCE( Salary ,"Not Salary") as SalaryCheck
FROM person;
#Видалимио після демонстрації
DELETE FROM person WHERE Table_number =7;
# Завдання - 6
SELECT FIO_person FROM person
UNION
SELECT Depa...
